package com.ooho.lesson05.mvc.dao;

import com.ooho.lesson05.mvc.model.SiteBean;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by hugohoo on 10/11/16.
 */
public class SiteDAO implements DAO<SiteBean, Integer> {
    private Connection conn;

    {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/shxt_lesson05";
            conn = DriverManager.getConnection(url, "root", "root");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public SiteBean find(Integer id) {
        SiteBean sb = new SiteBean();

        try(PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM sites WHERE id = ?")){
            pstmt.setInt(1, id);
            try(ResultSet rs = pstmt.executeQuery()){
                if(rs != null){
                    rs.next();

                    sb.setTitle(rs.getString("title"));
                    sb.setUrl(rs.getString("url"));
                    sb.setId(rs.getInt("id"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return sb;
    }

    @Override
    public List<SiteBean> find() {
        List<SiteBean> list = new LinkedList<SiteBean>();

        try(Statement stmt = conn.createStatement()) {

            try(ResultSet rs = stmt.executeQuery("SELECT * FROM sites")) {
                while(rs.next()){
                    SiteBean sb = new SiteBean();
                    sb.setId(rs.getInt("id"));
                    sb.setTitle(rs.getString("title"));
                    sb.setUrl(rs.getString("url"));

                    list.add(sb);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public Integer insert(SiteBean value) {
        int rslt = -1;

        try(PreparedStatement pstmt = conn.prepareStatement("INSERT INTO sites (title, url) values (?, ?)")) {
            pstmt.setString(1, value.getTitle());
            pstmt.setString(2, value.getUrl());

            rslt = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rslt;
    }

    @Override
    public int update(SiteBean value) {
        int rslt = -1;

        try(PreparedStatement pstmt = conn.prepareStatement("UPDATE sites SET title = ?, url = ? WHERE id = ? ")) {
            pstmt.setString(1, value.getTitle());
            pstmt.setString(2, value.getUrl());
            pstmt.setInt(3, value.getId());

            rslt = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rslt;
    }

    @Override
    public int delete(SiteBean value) {
        int rslt = -1;

        try(PreparedStatement pstmt = conn.prepareStatement("DELETE FROM sites WHERE id = ?")){
            pstmt.setInt(1, value.getId());

            rslt = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rslt;
    }

    public void close(){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
